﻿Imports System.Data.Linq
Imports System.Globalization
Imports NewHeaderSystems.Archive.Types
Imports NewHeaderSystems.Archive.Core
Imports System.Configuration
Imports System.Data.SqlClient

Public Class DataScript

    ''' <summary>
    ''' Flag to indicate that we don't want to limit the number of rows returned by the query for unassigned confirms.
    ''' </summary>
    Public Const ALL_ROWS As Integer = 0

    Public Const CURRENT_DATABASE_VERSION As Integer = 4 'Increment this version number each time a new script is added.
    Private Const CONNECTION_STRING_FORMAT_FILE As String = "Data Source=.\SQLEXPRESS;AttachDbFilename={0};Integrated Security=True;User Instance=True"
    Private Const CONNECTION_STRING_FORMAT_SERVER As String = "Server={0};Database={1};Trusted_Connection=True;"

    Private Shared _connectionString As String
    Public Shared Property ConnectionString() As String
        Get
            If String.IsNullOrEmpty(_connectionString) Then
                _connectionString = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None).ConnectionStrings.ConnectionStrings("NewHeaderSystems.Archive.Data.My.MySettings.NHSArchiveConnectionString").ConnectionString
            End If
            Return _connectionString
        End Get
        Set(ByVal value As String)
            _connectionString = value
        End Set
    End Property

    Public Shared Function IsValidConnection(ByVal connectionString As String, ByRef errorMessage As String) As Boolean
        Dim isValid As Boolean = False
        Try
            Dim db = GetArchiveDataContext(connectionString)

            Dim settings = From p In db.SysSettings Where p.ID = 1
            isValid = settings.Count >= 0
        Catch ex As Exception
            errorMessage = ex.Message
            'TODO Write to log
        End Try
        Return isValid
    End Function

    Public Shared Function IsValidServer(ByVal serverName As String, ByRef errorMessage As String) As Boolean
        Dim isValid As Boolean = False
        Try
            Dim connectionString = GetConnectionString(serverName, "master")
            Dim db = GetArchiveDataContext(connectionString)

            db.ExecuteCommand("SELECT * FROM sysobjects WHERE ID = 0") 'Just a query that returns no rows
            isValid = True
        Catch ex As Exception
            errorMessage = ex.Message
            'TODO Write to log
        End Try
        Return isValid
    End Function

    Public Shared Function IsExistingDatabase(ByVal serverName As String, ByVal database As String, ByRef errorMessage As String) As Boolean
        Dim isExists As Boolean = False
        Try
            Dim connectionString = GetConnectionString(serverName, "master")
            Dim sql = String.Format("SELECT DB_ID('{0}')", database)

            Using conn As New SqlConnection(connectionString)
                Dim cmd As New SqlCommand(sql, conn)

                conn.Open()
                isExists = Not (DBNull.Value.Equals(cmd.ExecuteScalar()))
            End Using
        Catch ex As Exception
            errorMessage = ex.Message
            'TODO Write to log
        End Try
        Return isExists
    End Function


    Public Shared Function CreateEmptyDatabase(ByVal server As String, ByVal database As String, ByRef errorMessage As String) As Boolean
        Dim created As Boolean = False

        Try
            Dim db As ArchiveDataContext
            Dim connectionString As String = GetConnectionString(server, "master")
            db = GetArchiveDataContext(connectionString)

            Dim sql = String.Format("CREATE DATABASE {0}", database)
            db.ExecuteCommand(sql)
            created = True
        Catch ex As Exception
            errorMessage = ex.Message
        End Try
        Return created
    End Function

    ''' <summary>
    ''' Loads the System Settings from the database
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Function GetSystemSettings() As SysSetting
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        'db.Log = Console.Out
        Dim settings = From p In db.SysSettings Where p.ID = 1

        If settings.Count = 0 Then
            Throw New Exception("Missing system settings")
        End If

        Return settings.First
    End Function

    Public Shared Function GetConfirm(ByVal confirmID As Integer) As Confirm
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim confirms = From p In db.Confirms Where p.ID = confirmID

        If confirms.Count = 0 Then
            Throw New Exception("Missing confirm")
        End If

        Return confirms.First
    End Function

    Public Shared Sub SaveSystemSettings(ByVal newSettings As SysSetting)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        db.SysSettings.Attach(newSettings)
        db.Refresh(RefreshMode.KeepCurrentValues, newSettings)
        db.SubmitChanges()
    End Sub

    ''' <summary>
    ''' Empties the temporary match table
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub ClearMatchTable()
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        db.ExecuteCommand("TRUNCATE TABLE MatchTable")
    End Sub

    Public Shared Sub CheckAndInsertAccount(ByVal accountID As String, ByVal accountName As String, ByVal custodianID As String)
        If String.IsNullOrEmpty(accountID) Or String.IsNullOrEmpty(custodianID) Then Return 'Don't insert empty values
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim accounts = From p In db.AccountMasters Where p.AccountID = accountID And p.CustodianID = custodianID

        If accounts.Count = 0 Then
            Dim newAccount As New AccountMaster With {.AccountId = accountID, .AccountName = accountName, .CustodianID = custodianID}
            db.AccountMasters.InsertOnSubmit(newAccount)
            db.SubmitChanges()
        End If
    End Sub

    Public Shared Sub CheckAndInsertSecurity(ByVal ticker As String, ByVal cusip As String, ByVal description As String)
        If String.IsNullOrEmpty(ticker) Or String.IsNullOrEmpty(cusip) Then Return 'Don't insert empty values
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim securities = From p In db.SecurityMasters Where p.Cusip = cusip And p.Ticker = ticker

        If securities.Count = 0 Then
            Dim newSecurity As New SecurityMaster With {.Cusip = cusip, .Ticker = ticker, .Description = description}
            db.SecurityMasters.InsertOnSubmit(newSecurity)
            db.SubmitChanges()
        End If
    End Sub

    Public Shared Function AccountIDAlreadyExists(ByVal accountID As String, ByVal custodianID As String, ByRef existingcustodianID As String) As Boolean
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim accounts = From p In db.AccountMasters Where p.AccountID = accountID
        Dim exists As Boolean = accounts.Count > 0
        If exists Then
            existingcustodianID = accounts.First.CustodianID
        End If
        Return exists
    End Function

    Public Shared Function TickerAlreadyExists(ByVal ticker As String, ByVal cusip As String, ByRef existingCusip As String) As Boolean
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim securities = From p In db.SecurityMasters Where p.Ticker = ticker
        Dim exists As Boolean = securities.Count > 0
        If exists Then
            existingCusip = securities.First.Cusip
        End If
        Return exists
    End Function


    Public Shared Sub InsertMatch(ByVal match As Match)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim cultureInfo As CultureInfo = New CultureInfo("en-US")

        Dim newMatch As New MatchTable With { _
            .ControlNum = match.ControlNum, _
            .ControlType = match.ControlType, _
            .AccountID = match.AccountID, _
            .TradeDate = DateTime.Parse(match.TradeDate, cultureInfo), _
            .Ticker = match.Ticker, _
            .Cusip = match.CUSIP, _
            .Status = match.Status, _
            .TxnType = match.TxnType}

        db.MatchTables.InsertOnSubmit(newMatch)
        db.SubmitChanges()
    End Sub

    Public Shared Function InsertTradeSuites(ByVal tradeSuites As List(Of TradeSuite), ByVal userID As Integer, ByVal sourceFilePath As String) As Integer
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim archive As New ArchiveHistory
        archive.UserID = userID
        archive.DateArchived = Date.Now
        archive.IsDeleted = False
        archive.SourceFilePath = sourceFilePath

        For Each trade In tradeSuites
            archive.Confirms.Add(CreateConfirm(trade))
        Next
        db.ArchiveHistories.InsertOnSubmit(archive)
        db.SubmitChanges()
        Return archive.ID
    End Function

#Region "Mapping"
    Private Shared Function CreateConfirm(ByVal trade As TradeSuite) As Confirm

        Dim newConfirm As New Confirm With { _
            .AccountID = trade.AccountID, _
            .AccountType = trade.AccountType, _
            .AccrBegin = trade.AccrBegin, _
            .AccrEnd = trade.AccrEnd, _
            .AccruedInterest = trade.AccruedInterest, _
            .ActivityDate = trade.ActivityDate, _
            .ActivityTime = trade.ActivityTime, _
            .AgtAccountNum = trade.AgtAccountNum, _
            .AgtName = trade.AgtName, _
            .AgtNum = trade.AgtNum, _
            .BkrDlrAccountNum = trade.BkrDlrAccountNum, _
            .BkrDlrBIC = trade.BkrDlrBIC, _
            .BkrDlrName = trade.BkrDlrName, _
            .BkrDlrNum = trade.BkrDlrNum, _
            .ClrAgtName = trade.ClrAgtName, _
            .ClrAgtNum = trade.ClrAgtNum, _
            .ClrBkrName = trade.ClrBkrName, _
            .ClrBkrNum = trade.ClrBkrNum, _
            .Commission = trade.Commission, _
            .ConfirmType = trade.ConfirmType, _
            .ControlNum = trade.ControlNum, _
            .Currency = trade.Currency, _
            .DTCControlNumber = trade.DTCControlNumber, _
            .DTCStatus = trade.DTCStatus, _
            .ExpSecurityType = trade.ExpSecurityType, _
            .SecurityDescription = trade.SecurityDescription, _
            .SecurityDescription2 = trade.SecurityDescription2, _
            .SecurityDescription3 = trade.SecurityDescription3, _
            .Fee = trade.Fee, _
            .InstitutionID = trade.InstitutionID, _
            .InstitutionName = trade.InstitutionName, _
            .InterestedParty1 = trade.InterestedParty1, _
            .InterestedParty1Name = trade.InterestedParty1Name, _
            .InterestedParty2 = trade.InterestedParty2, _
            .InterestedParty2Name = trade.InterestedParty2Name, _
            .Market = trade.Market, _
            .NetAmount = trade.NetAmount, _
            .NetPrice = trade.NetPrice, _
            .OriginalBkrControlNum = trade.OriginalBkrControlNum, _
            .OtherCharges = trade.OtherCharges, _
            .PricePerShare = trade.PricePerShare, _
            .Principal = trade.Principal, _
            .ProcessDate = trade.ProcessDate, _
            .ProcessTime = trade.ProcessTime, _
            .Reg_Shp = trade.Reg_Shp, _
            .RetrieveSubtype = trade.RetrieveSubtype, _
            .Role = trade.Role, _
            .SecurityID = trade.SecurityID, _
            .SecurityIndicator = trade.SecurityIndicator, _
            .SettlementCode = trade.SettlementCode, _
            .SettlementDate = trade.SettlementDate, _
            .Side = trade.Side, _
            .SpecialInstr1 = trade.SpecialInstr1, _
            .SpecialInstr2 = trade.SpecialInstr2, _
            .SpecialInstr3 = trade.SpecialInstr3, _
            .SpecialInstr4 = trade.SpecialInstr4, _
            .SpecialInstr5 = trade.SpecialInstr5, _
            .SettlementLocation = trade.SettlementLocation, _
            .Taxes = trade.Taxes, _
            .TickerSymbol = trade.TickerSymbol, _
            .TradeDate = trade.TradeDate, _
            .Units = trade.Units, _
            .Source = trade.Source, _
            .HeadingLine = trade.HeadingLine, _
            .AffirmStatus = trade.AffirmStatus, _
            .EligIndicator = trade.EligIndicator, _
            .CancelIndicator = .CancelIndicator}
        Return newConfirm

    End Function
#End Region

    Public Shared Sub UpdateTradeFromMatch(ByVal trade As TradeSuite)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim matches = From p In db.MatchTables Where p.ControlNum = trade.ControlNum
        If matches.Count <> 0 Then
            With matches.First
                trade.AccountID = .AccountID
                trade.TickerSymbol = .Ticker
            End With
        End If
    End Sub

    Public Shared Sub UpdateTradeFromAccount(ByVal trade As TradeSuite)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim matches = From p In db.AccountMasters Where p.CustodianID = trade.AgtAccountNum
        If matches.Count <> 0 Then
            trade.AccountID = matches.First.AccountID
        End If
    End Sub

    Public Shared Sub UpdateTradeFromSecurity(ByVal trade As TradeSuite)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim matches = From p In db.SecurityMasters Where p.Cusip = trade.SecurityID
        If matches.Count <> 0 Then
            trade.TickerSymbol = matches.First.Ticker
        End If
    End Sub

    Public Shared Function GetSearchResults(ByVal searchOnArchiveDate As Boolean, ByVal archiveDateFrom As Date, ByVal archiveDateTo As Date, _
                                                    ByVal searchOnTradeDate As Boolean, ByVal tradeDateFrom As Date, ByVal tradeDateTo As Date, _
                                                    ByVal accountID As String, ByVal custodianAccountID As String, ByVal ticker As String, ByVal cusip As String, _
                                                    ByVal source As String, ByVal transactionType As String, ByVal dtcControlNumber As String, ByVal execBrokerNumber As String, ByVal includeUnassignedOnly As Boolean, ByVal archiveHistoryList As List(Of String), ByVal rowCount As Integer, ByRef totalRowCount As Integer) As List(Of ConfirmView)
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim sql As String = BuildSearchSql(searchOnArchiveDate, archiveDateFrom, archiveDateTo, _
                                                    searchOnTradeDate, tradeDateFrom, tradeDateTo, _
                                                    accountID, custodianAccountID, ticker, cusip, _
                                                    source, transactionType, dtcControlNumber, execBrokerNumber, includeUnassignedOnly, archiveHistoryList, ALL_ROWS)
        Dim confirms As IEnumerable(Of Confirm) = db.ExecuteQuery(Of Confirm)(sql)
        Dim confirmsView As New List(Of ConfirmView)

        Dim index = 0
        For Each confirm In confirms
            If (index + 1 <= rowCount Or rowCount = ALL_ROWS) Then
                confirmsView.Add(New ConfirmView With { _
                                 .ID = confirm.ID, _
                                 .AccountID = confirm.AccountID, _
                                 .CustodianAccountID = confirm.AgtAccountNum, _
                                 .TickerSymbol = confirm.TickerSymbol, _
                                 .Cusip = confirm.SecurityID, _
                                 .TradeDate = confirm.TradeDate, _
                                 .ArchiveDate = confirm.ArchiveHistory.DateArchived, _
                                 .Shares = confirm.Units, _
                                 .NetAmount = confirm.NetAmount, _
                                 .ExecutingBrokerID = confirm.BkrDlrNum})
            End If
            index = index + 1
        Next
        totalRowCount = index
        Return confirmsView
    End Function

    Private Shared Function BuildSearchSql(ByVal searchOnArchiveDate As Boolean, ByVal archiveDateFrom As Date, ByVal archiveDateTo As Date, _
                                                ByVal searchOnTradeDate As Boolean, ByVal tradeDateFrom As Date, ByVal tradeDateTo As Date, _
                                                ByVal accountID As String, ByVal custodianAccountID As String, ByVal ticker As String, ByVal cusip As String, _
                                                ByVal source As String, ByVal transactionType As String, ByVal dtcControlNumber As String, ByVal execBrokerNumber As String, ByVal includeUnassignedOnly As Boolean, ByVal archiveHistoryList As List(Of String), ByVal rowCount As Integer) As String
        Dim topClause As String = IIf(rowCount > 0, "TOP " + rowCount.ToString(), "")
        Dim sql As New SqlBuilder(String.Format("SELECT {0} C.* FROM Confirms C INNER JOIN ArchiveHistory AH ON AH.ID = C.ArchiveHistoryID AND AH.IsDeleted = 'FALSE'", topClause))
        If searchOnTradeDate Then
            'Inner CONVERT casts string to date, outer CONVERT takes only date part (to avoid including time when comparing between dates
            sql.AppendCriteria("ISDATE(TradeDate) = 1 AND CONVERT(DATETIME, CONVERT(DATETIME, TradeDate), 101)  BETWEEN '{0}' AND '{1}'", tradeDateFrom.ToString("yyyyMMdd"), tradeDateTo.AddDays(1).ToString("yyyyMMdd"))
        End If
        If searchOnArchiveDate Then
            sql.AppendCriteria("AH.DateArchived BETWEEN '{0}' AND '{1}'", archiveDateFrom.ToString("yyyyMMdd"), archiveDateTo.AddDays(1).ToString("yyyyMMdd"))
        End If
        AppendParameter(sql, "Source", source)
        AppendParameter(sql, "AccountID", accountID)
        AppendParameter(sql, "AgtAccountNum", custodianAccountID)
        AppendParameter(sql, "TickerSymbol", ticker)
        AppendParameter(sql, "SecurityID", cusip)
        AppendParameter(sql, "DtcControlNumber", dtcControlNumber)
        AppendParameter(sql, "BkrDlrNum", execBrokerNumber)
        AppendParameter(sql, "Side", transactionType)
        If includeUnassignedOnly Then
            sql.AppendCriteria("(AccountID = '' OR TickerSymbol = '')")
        End If
        If Not archiveHistoryList Is Nothing Then
            Dim list = String.Join(",", archiveHistoryList.ToArray())
            sql.AppendCriteria("ArchiveHistoryID IN ({0})", list)
        End If
        Return sql.ToString()
    End Function

    Private Shared Sub AppendParameter(ByVal sql As SqlBuilder, ByVal name As String, ByVal value As String)
        Dim op As String
        If Not String.IsNullOrEmpty(value) Then
            op = IIf(value.Contains("*"), "LIKE", "=")

            sql.AppendCriteria("{0} {1} '{2}'", name, op, SqlBuilder.GetSafeWildCardValue(value))
        End If
    End Sub

    Public Shared Function GetArchiveHistory(ByVal month As Date) As List(Of ArchiveHistoryView)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim archiveSql = "SELECT AH.ID, AH.DateArchived, AH.SourceFilePath, "
        archiveSql += "(SELECT COUNT(*) FROM Confirms C WHERE C.ArchiveHistoryID = AH.ID AND (C.AccountID IS NULL OR C.AccountID = '' OR C.TickerSymbol IS NULL OR C.TickerSymbol = '')) UnassignedCount,"
        archiveSql += "(SELECT COUNT(*) FROM Confirms C WHERE C.ArchiveHistoryID = AH.ID) TotalConfirms "
        archiveSql += "FROM ArchiveHistory AH WHERE AH.IsDeleted = 'False' AND DATEDIFF(m, AH.DateArchived, '{0}') = 0"
        archiveSql = String.Format(archiveSql, month.ToString("dd-MMM-yyyy"))
        Return db.ExecuteQuery(Of ArchiveHistoryView)(archiveSql).ToList()
    End Function

    Public Shared Sub DeleteArchiveHistory(ByVal archiveHistoryID As Integer, ByVal userID As Integer)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim archives = From p In db.ArchiveHistories Where p.ID = archiveHistoryID
        With archives.First
            .IsDeleted = True
            .DateDeleted = Date.Now
            .DeletedByUserID = userID
        End With
        db.SubmitChanges()
    End Sub

    ''' <summary>
    ''' Empties the confirms table
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub ClearConfirms()
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        db.ExecuteCommand("DELETE FROM Confirms")
        db.ExecuteCommand("DELETE FROM ArchiveHistory")
    End Sub


    Public Shared Sub UpdateConfirmsWithAccountID(ByVal accountID As String, ByVal custodianID As String)

        CheckAndInsertAccount(accountID, "", custodianID)

        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim sqlCommand As String = String.Format("UPDATE Confirms SET AccountID='{0}' WHERE (AccountID IS NULL OR AccountID = '') AND AgtAccountNum = '{1}'", accountID, custodianID)
        db.ExecuteCommand(sqlCommand)
    End Sub

    Public Shared Sub UpdateConfirmsWithTicker(ByVal ticker As String, ByVal cusip As String)
        CheckAndInsertSecurity(ticker, cusip, "")

        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim sqlCommand As String = String.Format("UPDATE Confirms SET TickerSymbol='{0}' WHERE SecurityID = '{1}'", ticker, cusip)
        db.ExecuteCommand(sqlCommand)
    End Sub

    Public Shared Sub GetSystemStatistics(ByRef lastArchived As Date, ByRef totalCompleted As Integer, ByRef unassignedCount As Integer)
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        unassignedCount = (From c In db.Confirms Where c.ArchiveHistory.IsDeleted = False And (c.AccountID Is Nothing Or c.AccountID = "" Or c.TickerSymbol Is Nothing Or c.TickerSymbol = "")).Count

        totalCompleted = (From c In db.Confirms Where c.ArchiveHistory.IsDeleted = False).Count

        lastArchived = GetLastArchived()

    End Sub

    Public Shared Function GetLastArchived() As Date
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        'HACK: Need to figure out how to handle null returns in vb
        Dim lastArchived As DateTime = Date.MinValue
        If ((From a In db.ArchiveHistories Where a.IsDeleted = False).Count > 0) Then
            lastArchived = (From a In db.ArchiveHistories Where a.IsDeleted = False Select a.DateArchived).Max
        End If
        Return lastArchived
    End Function
    Public Shared Sub GetSummary(ByVal archiveIdList As List(Of String), ByRef totalCompleted As Integer, ByRef unassignedCount As Integer)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        db.Log = Console.Out
        unassignedCount = (From c In db.Confirms Where archiveIdList.Contains(c.ArchiveHistoryID) And c.ArchiveHistory.IsDeleted = False And (c.AccountID Is Nothing Or c.AccountID = "" Or c.TickerSymbol Is Nothing Or c.TickerSymbol = "")).Count
        totalCompleted = (From c In db.Confirms Where archiveIdList.Contains(c.ArchiveHistoryID) And c.ArchiveHistory.IsDeleted = False).Count
        'Dim db As ArchiveDataContext = GetArchiveDataContext()
        'Dim archiveSql = "SELECT AH.ID, AH.DateArchived, AH.SourceFilePath, "
        'archiveSql += "(SELECT COUNT(*) FROM Confirms C WHERE C.ArchiveHistoryID = AH.ID AND (C.AccountID IS NULL OR C.AccountID = '' OR C.TickerSymbol IS NULL OR C.TickerSymbol = '')) UnassignedCount,"
        'archiveSql += "(SELECT COUNT(*) FROM Confirms C WHERE C.ArchiveHistoryID = AH.ID) TotalConfirms "
        'archiveSql += "FROM ArchiveHistory AH WHERE AH.IsDeleted = 'False' AND DATEDIFF(m, AH.DateArchived, '{0}') = 0"
        'archiveSql = String.Format(archiveSql, Month.ToString("dd-MMM-yyyy"))
        'Return db.ExecuteQuery(Of ArchiveHistoryView)(archiveSql).ToList()

    End Sub

    Public Shared Function GetAccounts() As List(Of AccountView)
        Return GetAccounts(String.Empty, String.Empty, String.Empty)
    End Function

    Public Shared Function GetAccounts(ByVal accountID As String, ByVal custodianID As String, ByVal accountName As String) As List(Of AccountView)
        Dim sql As New SqlBuilder("SELECT ID, AccountID, CustodianID, AccountName FROM AccountMaster")
        If Not String.IsNullOrEmpty(accountID) Then
            AppendParameter(sql, "AccountID", accountID)
        End If
        If Not String.IsNullOrEmpty(custodianID) Then
            AppendParameter(sql, "CustodianID", custodianID)
        End If
        If Not String.IsNullOrEmpty(accountName) Then
            AppendParameter(sql, "AccountName", accountName)
        End If

        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Return db.ExecuteQuery(Of AccountView)(sql.ToString()).ToList()
    End Function

    Public Shared Sub DeleteAccount(ByVal itemID As Integer)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim accounts = From a In db.AccountMasters Where a.ID = itemID
        db.AccountMasters.DeleteOnSubmit(accounts.First)
        db.SubmitChanges()
    End Sub

    Public Shared Sub SaveAccount(ByVal ID As Integer, ByVal accountID As String, ByVal custodianID As String, ByVal accountName As String)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim newItem As AccountMaster
        If ID > 0 Then 'update
            Dim accounts = From p In db.AccountMasters Where p.ID = ID
            newItem = accounts.First
        Else
            newItem = New AccountMaster
            db.AccountMasters.InsertOnSubmit(newItem)
        End If
        With newItem
            .AccountID = accountID
            .CustodianID = custodianID
            .AccountName = accountName
        End With
        db.SubmitChanges()
    End Sub

    Public Shared Function IsExistingAccountAndCustodian(ByVal accountID As String, ByVal custodianID As String) As Boolean
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim accounts = From p In db.AccountMasters Where p.AccountID = accountID And p.CustodianID = custodianID

        Return accounts.Count > 0
    End Function

    Public Shared Function IsExistingCustodian(ByVal ID As Integer, ByVal custodianID As String) As Boolean
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim accounts = From p In db.AccountMasters Where p.CustodianID = custodianID And p.ID <> ID

        Return accounts.Count > 0
    End Function

    Public Shared Sub LoadAccounts(ByVal accountsList As List(Of AccountView))
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        For Each account As AccountView In accountsList
            If IsExistingCustodian(0, account.CustodianID) Then
                Throw New Exception(String.Format("Custodian ID '{0}' already exists.", account.CustodianID))
            End If

            Dim newAccount As New AccountMaster With {.AccountId = account.AccountID, .AccountName = account.AccountName, .CustodianID = account.CustodianID}
            db.AccountMasters.InsertOnSubmit(newAccount)

        Next
        db.SubmitChanges()
    End Sub

    Public Shared Function GetSecurities() As List(Of SecurityView)
        Return GetSecurities(String.Empty, String.Empty, String.Empty)
    End Function

    Public Shared Function GetSecurities(ByVal ticker As String, ByVal cusip As String, ByVal description As String) As List(Of SecurityView)
        Dim sql As New SqlBuilder("SELECT ID, Ticker, Cusip, Description FROM SecurityMaster")
        If Not String.IsNullOrEmpty(ticker) Then
            AppendParameter(sql, "Ticker", ticker)
        End If
        If Not String.IsNullOrEmpty(cusip) Then
            AppendParameter(sql, "Cusip", cusip)
        End If
        If Not String.IsNullOrEmpty(description) Then
            AppendParameter(sql, "Description", description)
        End If

        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Return db.ExecuteQuery(Of SecurityView)(sql.ToString()).ToList()
    End Function

    Public Shared Sub DeleteSecurity(ByVal itemID As Integer)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim securities = From a In db.SecurityMasters Where a.ID = itemID
        db.SecurityMasters.DeleteOnSubmit(securities.First)
        db.SubmitChanges()
    End Sub

    Public Shared Sub SaveSecurity(ByVal ID As Integer, ByVal ticker As String, ByVal cusip As String, ByVal description As String)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim newItem As SecurityMaster
        If ID > 0 Then 'update
            Dim accounts = From p In db.SecurityMasters Where p.ID = ID
            newItem = accounts.First
        Else
            newItem = New SecurityMaster
            db.SecurityMasters.InsertOnSubmit(newItem)
        End If
        With newItem
            .Ticker = ticker
            .Cusip = cusip
            .Description = description
        End With
        db.SubmitChanges()
    End Sub

    Public Shared Function IsExistingTickerCusip(ByVal ticker As String, ByVal cusip As String) As Boolean
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim securities = From p In db.SecurityMasters Where p.Ticker = ticker And p.Cusip = cusip

        Return securities.Count > 0
    End Function

    Public Shared Function IsExistingTicker(ByVal ID As Integer, ByVal ticker As String) As Boolean
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim securities = From p In db.SecurityMasters Where p.Ticker = ticker And p.ID <> ID

        Return securities.Count > 0
    End Function

    Public Shared Function IsExistingCusip(ByVal ID As Integer, ByVal cusip As String) As Boolean
        Dim db As ArchiveDataContext = GetArchiveDataContext()

        Dim securities = From p In db.SecurityMasters Where p.Cusip = cusip And p.ID <> ID

        Return securities.Count > 0
    End Function

    Public Shared Sub LoadSecurities(ByVal accountsList As List(Of SecurityView))
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim count As Integer = 0
        For Each security As SecurityView In accountsList
            count = count + 1
            If IsExistingTicker(0, security.Ticker) Then
                Throw New Exception(String.Format("Ticker '{0}' already exists.", security.Ticker))
            ElseIf IsExistingCusip(0, security.Cusip) Then
                Throw New Exception(String.Format("CUSIP '{0}' already exists.", security.Cusip))
            End If

            Dim newSecurity As New SecurityMaster With {.Ticker = security.Ticker, .Cusip = security.Cusip, .Description = security.Description}
            db.SecurityMasters.InsertOnSubmit(newSecurity)

        Next
        db.SubmitChanges()
    End Sub

    Public Shared Function GetTransactionTypes() As IEnumerable(Of String)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim transactionTypes As IEnumerable(Of String) = From p In db.TxnTypeCodes Select p.LongDesc
        Return transactionTypes
    End Function

    Public Shared Sub ExecuteCommand(ByVal sqlCommand As String)
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        db.ExecuteCommand(sqlCommand)
    End Sub

    Public Shared Function GetArchiveDataContext(ByVal connection As String) As ArchiveDataContext
        Return New ArchiveDataContext(connection)
    End Function

    Public Shared Function GetArchiveDataContext() As ArchiveDataContext
        Return New ArchiveDataContext(ConnectionString)
    End Function

    Public Shared Sub CreateDatabase(ByVal filePath As String)
        ZipHelper.ExtractZipFile("BlankDatabase.mdf", My.Resources.BlankDatabase, filePath)

        Dim currentversion = 0 'New database
        RunUpgradeScripts(currentversion, GetConnectionString(filePath))
    End Sub

    Public Shared Sub CreateDatabase(ByVal server As String, ByVal database As String)
        Dim currentversion = 0 'New database
        RunUpgradeScripts(currentversion, GetConnectionString(server, database))
    End Sub

    Public Shared Function GetConnectionString(ByVal filePath As String) As String
        Return String.Format(CONNECTION_STRING_FORMAT_FILE, filePath)
    End Function

    Public Shared Function GetConnectionString(ByVal server As String, ByVal database As String) As String
        Return String.Format(CONNECTION_STRING_FORMAT_SERVER, server, database)
    End Function

    Public Shared Sub CheckAndUpgradeDatabase()
        Dim currentVersion = GetCurrentDatabaseVersion()
        RunUpgradeScripts(currentVersion, String.Empty)
    End Sub

    Private Shared Sub RunUpgradeScripts(ByVal startingVersionNumber As Integer, ByVal connectionString As String)
        Dim db As ArchiveDataContext
        If String.IsNullOrEmpty(connectionString) Then
            db = GetArchiveDataContext()
        Else
            db = GetArchiveDataContext(connectionString)
        End If

        For index As Integer = startingVersionNumber + 1 To CURRENT_DATABASE_VERSION
            RunScript(index, db)
        Next
    End Sub

    Private Shared Function GetCurrentDatabaseVersion() As Integer
        Dim db As ArchiveDataContext = GetArchiveDataContext()
        Dim currentVersion As Integer
        Dim versions = From p In db.DatabaseVersionHistories Order By p.VersionNumber Descending Take 1

        If versions.Count = 0 Then
            currentVersion = 0
        Else
            currentVersion = versions.First.VersionNumber
        End If

        Return currentVersion

    End Function

    Private Shared Sub RunScript(ByVal version As Integer, ByVal db As ArchiveDataContext)
        Dim sql = My.Resources.ResourceManager.GetString("_" + version.ToString())
        db.ExecuteCommand(sql)

        Dim versionUpgradeSql = String.Format("INSERT INTO [DatabaseVersionHistory] ([VersionNumber],[UpdatedDate]) VALUES({0}, GETDATE())", version)
        db.ExecuteCommand(versionUpgradeSql)
    End Sub

    Public Shared Function ExecuteQuery(ByVal connectionString As String, ByVal query As String) As DataTable
        Dim da As New System.Data.SqlClient.SqlDataAdapter(query, connectionString)
        Dim table As New DataTable()
        da.Fill(table)
        Return table
    End Function

    Public Shared Function GetFileNameFromConnectionString(ByVal connectionString As String) As String
        Dim fileName As String = String.Empty
        Dim items = connectionString.Split(";")
        For Each item In items
            Dim itemPair = item.Split("=")
            If String.Compare(itemPair(0).Trim(), "attachdbfilename", StringComparison.OrdinalIgnoreCase) = 0 Then
                fileName = itemPair(1)
            End If
        Next
        Return fileName
    End Function
End Class